/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.database;

import br.com.models.Discipline;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Valdigleis
 */
public class DaoDiscipline {
    
    private String sql;
    
    private Connection connection;
    
    /**
     * Método construtor que implementa a classe
     * DaoDiscipline, que é responsavel pela comunicação
     * do sistema com a base de dados, para objetos
     * da classe Discipline.
     *  
     */
    public DaoDiscipline(){
        this.connection = Derby.getConnection();
    }
    
    /**
     * Método que grava a disciplina na base de dados.
     * 
     * @param discipline 
     */
    public void Save(Discipline discipline){
        try{
            sql = "INSERT INTO SAD.DISCIPLINE" +
                " (name, code, semester, credits)" + " values (?,?,?,?)";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, discipline.getName());
            stmt.setString(2, discipline.getCode());
            stmt.setInt(3, discipline.getSemester());
            stmt.setInt(4, discipline.getCredits());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    
    public boolean checkCode(String code){
        try{
            sql = "SELECT * FROM SAD.DISCIPLINE WHERE code=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, code);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                closeConnection();
                return true;
            }
            return false;
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        closeConnection();
        return false;
    }
    
    public Discipline getDisicipline(int idDiscipline){
        Discipline discipline = new Discipline();
        try{
            sql = "SELECT * FROM SAD.DISCIPLINE WHERE id=?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, idDiscipline);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                discipline.setId(rs.getInt("id"));
                discipline.setName(rs.getString("name"));
                discipline.setCode(rs.getString("code"));
                discipline.setSemester(rs.getInt("semester"));
                discipline.setCredits(rs.getInt("credits"));
                return discipline;
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<Discipline> getAllDiscipline(){
        ArrayList<Discipline> disciplines = new ArrayList<Discipline>();
        try{
            sql = "SELECT * FROM SAD.DISCIPLINE ORDER BY name";
            PreparedStatement stmt = connection.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                Discipline discipline = new Discipline();
                discipline.setId(rs.getInt("id"));
                discipline.setName(rs.getString("name"));
                discipline.setCode(rs.getString("code"));
                discipline.setSemester(rs.getInt("semester"));
                discipline.setCredits(rs.getInt("credits"));
                disciplines.add(discipline);
            }
            return disciplines;
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<Discipline> getAllDiscipline(int Semester){
        ArrayList<Discipline> disciplines = new ArrayList<Discipline>();
        try{
            sql = "SELECT * FROM SAD.DISCIPLINE WHERE semester=? ORDER BY name";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, Semester);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                Discipline discipline = new Discipline();
                discipline.setId(rs.getInt("id"));
                discipline.setName(rs.getString("name"));
                discipline.setCode(rs.getString("code"));
                discipline.setSemester(rs.getInt("semester"));
                discipline.setCredits(rs.getInt("credits"));
                disciplines.add(discipline);
            }
            return disciplines;
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public ArrayList<Discipline> getAllDiferentsOfDiscipline(int id){
        ArrayList<Discipline> disciplines = new ArrayList<Discipline>();
        try{
            sql = "SELECT * FROM SAD.DISCIPLINE WHERE id <> ?";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){
                Discipline discipline = new Discipline();
                discipline.setId(rs.getInt("id"));
                discipline.setName(rs.getString("name"));
                discipline.setCode(rs.getString("code"));
                discipline.setSemester(rs.getInt("semester"));
                discipline.setCredits(rs.getInt("credits"));
                disciplines.add(discipline);
            }
            return disciplines;
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
        return null;
    }
    
    public void update(Discipline discipline){
       try{
            sql =  "UPDATE SAD.DISCIPLINE SET name=?, code=?, semester=?, credits=? WHERE id=?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            stmt.setString(1, discipline.getName());
            stmt.setString(2, discipline.getCode());
            stmt.setInt(3, discipline.getSemester());
            stmt.setInt(4, discipline.getCredits());
            stmt.setInt(5, discipline.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        } 
    }
    
    public int getIdFinal(){
        try{
            sql =  "SELECT MAX(id) as id FROM SAD.DISCIPLINE";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            ResultSet rs = stmt.executeQuery(); 
            rs.next(); 
            int lastId = rs.getInt("id"); 
            rs.close(); 
            stmt.close();
            return lastId; 
        }catch(SQLException e){
            System.out.println(e.getMessage());
            return -1;
        }
    }
    
    public int getSemesterFinal(){
        try{
            sql =  "SELECT MAX(semester) as id FROM SAD.DISCIPLINE";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            ResultSet rs = stmt.executeQuery(); 
            rs.next(); 
            int lastId = rs.getInt("semester"); 
            rs.close(); 
            stmt.close();
            return lastId; 
        }catch(SQLException e){
            System.out.println(e.getMessage());
            return -1;
        }
    }
    
    public void remove(Discipline discipline){
        try{
            sql =  "DELETE FROM SAD.DISCIPLINE WHERE id=?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql); 
            stmt.setInt(1, discipline.getId());
            stmt.execute();
            stmt.close();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
    
    /**
     * Método que encerar a conexão com o banco.
     */
    public void closeConnection(){
        try{
            this.connection.close();
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
}
